﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace IBS
{
    public partial class IbsDataContext
    {
        /// <summary>
        /// Найти контрагента по уникальному номеру
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public Interfaces.ISupplier GetSupplierByGuid(Guid guid)
        {
            var sql = "select GUID_RECORD, SUPIBSCODE, SUPNAME from tbsupplier where GUID_RECORD = '{0}'";
            sql = String.Format(sql, guid.ToString("N").ToUpper());

            var db = new DB.Oracle(this.connectionString);
            var data = db.Excecute<Suppler>(sql);

            if (data.Any()) {
                return data.SingleOrDefault();
            }
            else {
                throw new Exception(String.Format("Контрагент с идентификатором {0} не найден", guid));
            }
        }

        /// <summary>
        /// Найти товар по уникальному номеру
        /// </summary>
        /// <param name="guid"></param>
        /// <returns></returns>
        public Interfaces.IItem GetItemByGuid(Guid guid)
        {
            var sql = "select GUID_RECORD, ITEMCODE, LONGNAME from tbitem where GUID_RECORD = '{0}'";
            sql = String.Format(sql, guid.ToString("N").ToUpper());

            var db = new DB.Oracle(this.connectionString);
            var data = db.Excecute<Item>(sql);

            if (data.Any())
            {
                return data.SingleOrDefault();
            }
            else
            {
                throw new Exception(String.Format("Товар с идентификатором {0} не найден", guid));
            }
        }

//        public IEnumerable<Interfaces.IItem> GetLastModifiedItems(DateTime lastSyncDate)
//        {
//            var sql = @"select 
//                            ITEMCODE, SUPIBSCODE, ITEMSTATCODE, LONGNAME, SHORTNAME, CHDATE 
//                        from 
//                            TBITEM 
//                        where 1 = 1
//                            --AND ITEMSTATCODE IN (1, 2)
//                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
//                        order by CHDATE";

//            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

//            return new DB
//                .Oracle(this.connectionString)
//                .Excecute<IbsItem>(sql);
//        }

        public IEnumerable<Interfaces.ILocation> GetLastModifiedTNKSite(DateTime lastSyncDate)
        {
            var sql = @"select GUID_Record, siteibscode, sitename, rccode,Address, dateupdate  from tbsite
                        where 1 = 1                            
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                            AND COMPANY	= 1	
                        order by dateupdate";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<IbsSite>(sql);
        }

        public IEnumerable<Interfaces.ILocation> GetLastModifiedBPSite(DateTime lastSyncDate)
        {
            var sql = @"select GUID_Record, siteibscode, sitename, rccode,Address, dateupdate  from tbsite
                        where 1 = 1                            
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                            AND COMPANY	= 2	
                        order by dateupdate";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<IbsSite>(sql);
        }

        public IEnumerable<Interfaces.ISupplier> GetLastModifiedTNKSupplier(DateTime lastSyncDate)
        {
            var sql = @"select 
                            GUID_RECORD,SUPIBSCODE, SUPNAME, CHDATE from tbsupplier
                        where 1 = 1          
                            AND  SUPPLIER_TYPE = 1	        
                            AND SUPIBSCODE > 5000       
                            AND CHDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<IbsSupplier>(sql);
        }


        public IEnumerable<Interfaces.ISupplier> GetLastModifiedBPSupplier(DateTime lastSyncDate)
        {
            var sql = @"select 
                            SUPIBSCODE, SUPNAME, CHDATE from tbsupplier
                        where 1 = 1          
                            AND  SUPPLIER_TYPE = 1	  
                             AND SUPIBSCODE >= 1000             
                            AND SUPIBSCODE < 5000
                            AND CHDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<IbsSupplier>(sql);
        }




        public IEnumerable<Interfaces.IItem> GetLastModifiedTNKItems(DateTime lastSyncDate)
        {
            var sql = @"select 
                            ITEMCODE, SUPIBSCODE, ITEMSTATCODE, LONGNAME, SHORTNAME, CHDATE 
                        from 
                            TBITEM 
                        where 1 = 1
                            AND ITEMSTATCODE IN (1, 2)
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                            AND SUPIBSCODE > 5000
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<IbsItem>(sql);
        }





        public IEnumerable<Interfaces.IItem> GetLastModifiedBPItems(DateTime lastSyncDate)
        {
            var sql = @"select 
                            ITEMCODE, SUPIBSCODE, ITEMSTATCODE, LONGNAME, SHORTNAME, CHDATE 
                        from 
                            TBITEM 
                        where 1 = 1
                            AND ITEMSTATCODE IN (1, 2)
                            AND DATEUPDATE >= TO_DATE('{0}', 'DD.MM.YYYY hh24:mi:ss')  
                             AND SUPIBSCODE >= 1000             
                            AND SUPIBSCODE < 5000
                        order by CHDATE";

            sql = String.Format(sql, lastSyncDate.ToString("dd.MM.yyyy hh:mm:ss"));

            return new DB
                .Oracle(this.connectionString)
                .Excecute<IbsItem>(sql);
        }

        public DateTime GetLastSyncDate(string paramName)
        {

            using (var connection = new OracleConnection(this.connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    var sql = @"select Value from edex.TBSETTING where Name = '{0}'";
                    sql = String.Format(sql, paramName);
                    command.CommandText = sql;
                    using (var reader = command.ExecuteReader())
                    {
                        reader.Read();
                        return DateTime.Parse(reader.GetString(0));
                    }
                }
            }
        }

        public void SetLastSyncDate(String LastSyncDate, string paramName)
        {

            using (var connection = new OracleConnection(this.connectionString))
            {
                connection.Open();
                using (var command = new OracleCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    var sql = @"update edex.TBSETTING set value = '{0}' where name = '{1}'";
                    sql = String.Format(sql,LastSyncDate, paramName);
                    command.CommandText = sql;
                    command.ExecuteReader();
                }
            }
        }

    }
}
